package ejemplo;

import java.sql.*;

public class AccesoDatos {

    private Connection cn = null;

    public AccesoDatos(String dbname)throws ClassNotFoundException,SQLException{
        Class.forName("com.mysql.jdbc.Driver");
        cn = DriverManager.getConnection("jdbc:mysql://localhost/"+dbname, "root", "");
    }

    public ResultSet getAllTemas()throws SQLException{ // debe retornar un ResultSet
        String sql = "Select * from temas";
        Statement st = cn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ResultSet rs = st.executeQuery(sql);
        return rs;
    }

    public ResultSet getLibros(String isbn, String titulo, String autor, String precio, String idtema)
    throws SQLException{

        if(isbn.length() > 0)
            isbn = "AND isbn LIKE \"%" + isbn + "%\" ";
        else isbn = "";

        if(titulo.length() > 0)
            titulo = "AND titulo LIKE \"%" + titulo + "%\" ";
        else titulo ="";

        if(autor.length() > 0)
            autor = "AND autor LIKE \"%" + autor + "%\" ";
        else autor ="";

        if(idtema.equals("99999"))
            idtema = "idtema > 0 ";
        else idtema ="idtema = " + idtema + " ";

        String sql = "Select isbn, titulo, autor, precio, tema " +
                     "from libros, temas " +
                     "where " + idtema + isbn + titulo + autor +
                     "and temas.id=libros.idtema";
        Statement st = cn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ResultSet rs = st.executeQuery(sql);
        return rs;
    }

    public ResultSet getTemas(String tema)throws SQLException{

        if(tema.equals(""))
            tema = "tema LIKE \"%\" ";
        else tema ="tema LIKE \"%" + tema + "%\" ";

        String sql = "Select id, tema " +
                     "from temas " +
                     "where " + tema ;
        Statement st = cn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ResultSet rs = st.executeQuery(sql);
        return rs;
    }


    public ResultSet getAllLibros()throws SQLException{ 
        String sql = "Select libros.id, isbn, titulo, autor, precio, tema " +
                     "from libros, temas " +
                     "where temas.id=libros.idtema";
        Statement st = cn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ResultSet rs = st.executeQuery(sql);
        return rs;
    }

        public ResultSet getLibro(String isbn)throws SQLException{ // debe retornar un ResultSet
        String sql = "Select libros.id, isbn, titulo, autor, precio, tema" +
                     " from libros, temas" +
                     " where " + isbn + "=libros.isbn" +
                     " and temas.id=libros.idtema";
        Statement st = cn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ResultSet rs = st.executeQuery(sql);
        return rs;
    }

        public ResultSet getTema(String id)throws SQLException{ // debe retornar un ResultSet
        String sql = "Select id, tema " +
                     "from temas " +
                     "where id=\"" + id + "\"";
        Statement st = cn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ResultSet rs = st.executeQuery(sql);
        return rs;
    }
         
        public int setLibro(String isbn, String titulo, String autor, String precio)throws SQLException{
        String sql = "UPDATE libros " +
                     "SET titulo='" + titulo + "', autor='" + autor + "', " +
                     "precio='" + precio + "' WHERE isbn='" + isbn + "'";
        Statement st = cn.createStatement();
        return st.executeUpdate(sql);
    }

        public int setTema(String id, String tema)throws SQLException{
        String sql = "UPDATE temas " +
                     "SET tema='" + tema + "' " +
                     "WHERE id='" + id + "'";
        Statement st = cn.createStatement();
        return st.executeUpdate(sql);
    }

    public int insertLibro(String isbn, String titulo, String autor, String precio, String idtema)
    throws SQLException{
        String sql = "INSERT INTO libros " +
                     "(isbn, titulo, autor, precio, idtema) " +
                     "VALUES (\"" + isbn + "\",\"" + titulo + "\",\"" + autor +
                     "\",\"" + precio + "\",\"" + idtema + "\")";
        Statement st = cn.createStatement();
        return st.executeUpdate(sql);
    }

    public int insertTema(String tema)throws SQLException{
        String sql = "INSERT INTO temas (tema) " +
                     "VALUES (\"" + tema + "\")";
        Statement st = cn.createStatement();
        return st.executeUpdate(sql);
    }

    public int delLibro(String isbn)throws SQLException{
        String sql = "DELETE FROM libros where isbn=\"" + isbn + "\"";
        Statement st = cn.createStatement();
        return st.executeUpdate(sql);
    }

    public int delTema(String id)throws SQLException{
        String sql = "DELETE FROM temas where id=\"" + id + "\"";
        Statement st = cn.createStatement();
        return st.executeUpdate(sql);
    }

    public void close()throws SQLException{
          cn.close();
    }
}
